#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
from MySQLdb.cursors import DictCursor
from app.common import singleton
from igor import logging
from igor import const 

@singleton
class DBUtils(object):
    """数据库操作工具类，包括连接数据库和关闭数据库
    以及执行sql语句等操作。
    """

    def connect(self):
        const.mysqldb.update({'charset': 'utf8'})
        conn = MySQLdb.connect(**const.mysqldb)
        return conn

    def close(self, conn, cur):
        try:
            if cur:
                cur.close()
            if conn:
                conn.close()
        except MySQLdb.Error, err:
            logging.log(err) 


    def execute_dict(self, sql, params=()):
        """SQL执行结果以字典的方式返回。
        """
        ret, conn, cur = None, None, None
        try:
            conn = self.connect()
            cur = conn.cursor(DictCursor)
            #cur.execute('SET AUTOCOMMIT=0') #默认已经关闭自动提交
            cur.execute('SET NAMES utf8')
            cur.execute(sql, params)
            conn.commit()
            ret = cur.fetchall()
        except MySQLdb.Error, err:
            logging.log(err)
        finally:
            self.close(conn, cur)
        return ret 

    def execute(self, sql, params=()):
        ret, conn, cur = None, None, None
        try:
            conn = self.connect()
            cur = conn.cursor()
            #cur.execute('SET AUTOCOMMIT=0')
            cur.execute('SET NAMES utf8')
            cur.execute(sql, params)
            conn.commit()
            ret = cur.fetchall()
        except MySQLdb.Error, err:
            logging.log(err)
        finally:
            self.close(conn, cur)
        return ret 

    def execute_affected(self, sql, params=()):
        """返回执行SQL语句后影响的行数。
        """
        ret, conn, cur = None, None, None
        try:
            conn = self.connect()
            cur = conn.cursor()
            #cur.execute('SET AUTOCOMMIT=0')
            cur.execute('SET NAMES utf8')
            cur.execute(sql, params)
            ret = conn.affected_rows() 
            conn.commit()
        except MySQLdb.Error, err:
            logging.log(err)
        finally:
            self.close(conn, cur)
        return ret 
